package com.mjgy.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mjgy.entity.BroadCastEntity;

/**
* @Description: TODO
* @author quanlq
* @date 2018年9月14日
* @version V1.0
*/
public interface BroadCastRepository extends JpaRepository<BroadCastEntity, Long>, JpaSpecificationExecutor<BroadCastEntity> { 

	void deleteByUserId(Long userId);
	
	BroadCastEntity findTopByUserId(Long userId);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE broad_cast SET is_deleted = :isDeleted WHERE user_id = :userId", nativeQuery = true)
	void updateStatus(@Param("isDeleted") Integer isDeleted, @Param("userId") Long userId);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE broad_cast SET like_num = like_num + 1 WHERE id = :broadCastId", nativeQuery = true)
	void increaseLikeNum(@Param("broadCastId") Long broadCastId);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE broad_cast SET sign_num = sign_num + 1 WHERE id = :broadCastId", nativeQuery = true)
	void increaseSignNum(@Param("broadCastId") Long broadCastId);
	
	@SuppressWarnings("rawtypes")
	@Query(value = "SELECT " +
		"bc.id" +
		",bc.type" +
		",bc.city" +
		",bc.date" +
		",bc.time" +
		",bc.description" +
		",bc.images" +
		",bc.forbid_images" +
		",bc.create_time" +
		",bc.like_num" +
		",bc.sign_num" +
		",bc.user_id" +
		",usr.nickname" +
		",usr.sex" +
		",usr.identity" +
		",usr.avatar" +
		",EXISTS(SELECT 1 FROM broast_cast_liker bcl WHERE bcl.broad_cast_id = bc.id AND bcl.liker_id = :viewerId) isLiker" +
		",EXISTS(SELECT 1 FROM broast_cast_signer bcs WHERE bcs.broad_cast_id = bc.id AND bcs.signer_id = :viewerId) isSigner" +
		",EXISTS(SELECT 1 FROM broast_cast_signer bcs WHERE bcs.broad_cast_id = bc.id AND bcs.is_dated = 1) isDating" +
		" FROM broad_cast bc" +
		" JOIN e_user usr ON bc.user_id = usr.id" +
		" WHERE is_deleted = 0 AND bc.user_id = :userId" +
		" ORDER BY bc.create_time DESC LIMIT 1", nativeQuery = true)
	List findByUserId(@Param("userId") Long userId, @Param("viewerId") Long viewerId);
	
	@SuppressWarnings("rawtypes")
	@Query(value = "SELECT " +
			"bc.id" +
			",bc.type" +
			",bc.city" +
			",bc.date" +
			",bc.time" +
			",bc.description" +
			",bc.images" +
			",bc.forbid_images" +
			",bc.create_time" +
			",bc.like_num" +
			",bc.sign_num" +
			",bc.user_id" +
			",usr.nickname" +
			",usr.sex" +
			",usr.identity" +
			",bc.is_deleted" +
			" FROM broad_cast bc" +
			" JOIN e_user usr ON bc.user_id = usr.id" +
			" WHERE bc.id = :broadCastId", nativeQuery = true)
	List findById(@Param("broadCastId") Long broadCastId);
	
	@Query(value = "SELECT COUNT(1) FROM broad_cast bc JOIN e_user usr ON bc.user_id = usr.id" +
			" WHERE usr.sex = :sex AND usr.nickname LIKE CONCAT('%', CONCAT(:nickname, '%'))", nativeQuery = true)
	Long countBySex(@Param("sex") Integer sex, @Param("nickname") String nickname);
	
}
